package com.service;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.dao.BusinessStatisticsDao;
import com.excel.ExcelUtil;
import com.pojo.BusinessStatistics;
import com.pojo.Qiye;
import com.pojo.Wuye;
import com.util.ValidateHelper;
import com.vo.BusinessStatisticsVo;
import com.vo.TrailVo;
import jxl.format.UnderlineStyle;
import jxl.write.*;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.io.File;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

@Service
public class BusinessStatisticsService extends ServiceImpl<BusinessStatisticsDao, BusinessStatistics> {

    @Autowired
    private BusinessStatisticsDao businessStatisticsDao;

    @Autowired
    private QiyeService qiyeService;

    @Autowired
    private WuyeService wuyeService;

    /**
     * 查询当天商户统计
     * @param businessId
     * @return
     */
    public BusinessStatistics getNowDateBusinessStatistics(String businessId) {
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        String nowDate = sdf.format(new Date());

        QueryWrapper q = new QueryWrapper();
        q.eq("business_id", businessId);
        q.eq("access_date", nowDate);
        q.last("limit 1");
        BusinessStatistics businessStatistics = businessStatisticsDao.selectOne(q);
        if (null != businessStatistics) {
            return businessStatistics;
        }
        return null;
    }

    /**
     * 获取物业统计列表
     * @param page
     * @param limit
     * @return
     */
    public IPage<BusinessStatisticsVo> getWyStatisticsList(String name, String start_time, String end_time, Integer page, Integer limit) {
        Page p = new Page(page,limit);
        if (ValidateHelper.isEmptyString(name)) {
            name = null;
        }
        if (ValidateHelper.isEmptyString(start_time)) {
            start_time = null;
        }
        if (ValidateHelper.isEmptyString(end_time)) {
            end_time = null;
        }
        IPage iPage = businessStatisticsDao.getWyStatistics(p, name, start_time, end_time);
        return iPage;
    }


    /**
     * 获取商户统计列表
     * @param page
     * @param limit
     * @return
     */
    public IPage<BusinessStatisticsVo> getBusinessStatisticsList(String name, String start_time, String end_time, String wyId, Integer page, Integer limit) {
        Page p = new Page(page,limit);
        if (ValidateHelper.isEmptyString(name)) {
            name = null;
        }
        if (ValidateHelper.isEmptyString(start_time)) {
            start_time = null;
        }
        if (ValidateHelper.isEmptyString(end_time)) {
            end_time = null;
        }
        if (ValidateHelper.isEmptyString(wyId)) {
            wyId = null;
        }
        IPage iPage = businessStatisticsDao.getBusinessStatistics(p, name, start_time, end_time, wyId);
        return iPage;
    }

    /**
     * 导出物业统计列表
     * @return
     */
    public List<BusinessStatisticsVo> getWyStatisticsByExport(String name, String start_time, String end_time) {
        if (ValidateHelper.isEmptyString(name)) {
            name = null;
        }
        if (ValidateHelper.isEmptyString(start_time)) {
            start_time = null;
        }
        if (ValidateHelper.isEmptyString(end_time)) {
            end_time = null;
        }
        List<BusinessStatisticsVo> staffStatisticsList = businessStatisticsDao.getWyStatisticsByExport(name, start_time, end_time);
        return staffStatisticsList;
    }

    /**
     * 导出商户统计列表
     * @return
     */
    public List<BusinessStatisticsVo> getBusinessStatisticsByExport(String name, String start_time, String end_time, String wyId) {
        if (ValidateHelper.isEmptyString(name)) {
            name = null;
        }
        if (ValidateHelper.isEmptyString(start_time)) {
            start_time = null;
        }
        if (ValidateHelper.isEmptyString(end_time)) {
            end_time = null;
        }
        if (ValidateHelper.isEmptyString(wyId)) {
            wyId = null;
        }
        List<BusinessStatisticsVo> staffStatisticsList = businessStatisticsDao.getBusinessStatisticsByExport(name, start_time, end_time, wyId);
        return staffStatisticsList;
    }

    /**
     * 商户访问详情记录导出Excel
     * @param fileName
     * @param sheetName
     * @param resultList
     * @throws Exception
     */
    public void exportExcelByBusinessStatisticsDetail(String fileName, String sheetName, List resultList) throws Exception {
        WritableWorkbook workbook = jxl.Workbook.createWorkbook(new File(fileName));
        WritableSheet sheet = workbook.createSheet(sheetName, 0);
        WritableFont wf = new WritableFont(WritableFont.TIMES,18,WritableFont.BOLD,true);
        WritableCellFormat wcf = new WritableCellFormat(wf);
        wcf.setBorder(jxl.format.Border.ALL,jxl.format.BorderLineStyle.THIN);
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss") ;
        //设置title
        List<String> titles = new ArrayList();
        titles.add("序号");
        titles.add("商户所属物业");
        titles.add("商户名称");
        titles.add("商户编号");
        titles.add("员工姓名");
        titles.add("员工编号");
        titles.add("员工所属公司");
        titles.add("员工所属物业");
        titles.add("地址");
        titles.add("入店时间");
        titles.add("离店时间");

        Label label=null;

        WritableCellFormat titleStyle = new WritableCellFormat(new WritableFont(WritableFont.createFont("微软雅黑"), 11, WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE, Colour.BLACK));
        titleStyle.setBackground(Colour.SEA_GREEN);
        titleStyle.setAlignment(Alignment.CENTRE);
        titleStyle.setVerticalAlignment(jxl.format.VerticalAlignment.getAlignment(1));
        titleStyle.setBorder(Border.NONE, BorderLineStyle.NONE, Colour.BLACK);

        WritableCellFormat colmStyle = new WritableCellFormat(new WritableFont(WritableFont.createFont("微软雅黑"), 10, WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE, Colour.BLACK));
//        title.setBackground(Colour.VERY_LIGHT_YELLOW);
        colmStyle.setAlignment(Alignment.CENTRE);
        colmStyle.setVerticalAlignment(jxl.format.VerticalAlignment.getAlignment(1));
        colmStyle.setBorder(Border.NONE, BorderLineStyle.NONE, Colour.BLACK);

        for(int i = 0;i < titles.size();i++){
            //x,y,第一行的列名
            label=new Label(i,0,titles.get(i),titleStyle);
            //7：添加单元格
            sheet.addCell(label);
        }

        for(int i = 1;i <= resultList.size();i++){
            TrailVo trailVo = (TrailVo) resultList.get(i - 1);
            label=new Label(0,i, String.valueOf(i),colmStyle);
            sheet.addCell(label);
            label=new Label(1,i, trailVo.getWyName(), colmStyle);
            sheet.addCell(label);
            label=new Label(2,i, trailVo.getBusiName(), colmStyle);
            sheet.addCell(label);
            label=new Label(3,i, trailVo.getBusiBh(), colmStyle);
            sheet.addCell(label);
            label=new Label(4,i, trailVo.getStaffName(), colmStyle);
            sheet.addCell(label);
            label=new Label(5,i, trailVo.getStaffBh(), colmStyle);
            sheet.addCell(label);
            Qiye qiye = qiyeService.getById(trailVo.getQyId());
            label=new Label(6,i, qiye.getQyname(), colmStyle);
            sheet.addCell(label);
            Wuye wuye = wuyeService.getById(qiye.getWyID());
            label=new Label(7,i, wuye.getWyname(), colmStyle);
            sheet.addCell(label);
            label=new Label(8,i, trailVo.getAddress(), colmStyle);
            sheet.addCell(label);
            label=new Label(9,i, sdf.format(trailVo.getRdTime()), colmStyle);
            sheet.addCell(label);
            String cdTime = "";
            if (null != trailVo.getCdTime()) {
                cdTime = sdf.format(trailVo.getCdTime());
            }
            label=new Label(10,i,  cdTime, colmStyle);
            sheet.addCell(label);
        }
        ExcelUtil.setColumnAutoSize(sheet,10);

        workbook.write();
        workbook.close();
    }

    /**
     * 物业访问记录导出Excel
     * @param fileName
     * @param sheetName
     * @param resultList
     * @throws Exception
     */
    public void exportExcelByWyStatistics(String fileName, String sheetName, List resultList) throws Exception {
        WritableWorkbook workbook = jxl.Workbook.createWorkbook(new File(fileName));
        WritableSheet sheet = workbook.createSheet(sheetName, 0);
        WritableFont wf = new WritableFont(WritableFont.TIMES,18,WritableFont.BOLD,true);
        WritableCellFormat wcf = new WritableCellFormat(wf);
        wcf.setBorder(jxl.format.Border.ALL,jxl.format.BorderLineStyle.THIN);
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd") ;
        //设置title
        List<String> titles = new ArrayList();
        titles.add("序号");
        titles.add("物业名称");
        titles.add("日期");
        titles.add("访问人数");

        Label label=null;

        WritableCellFormat titleStyle = new WritableCellFormat(new WritableFont(WritableFont.createFont("微软雅黑"), 11, WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE, Colour.BLACK));
        titleStyle.setBackground(Colour.SEA_GREEN);
        titleStyle.setAlignment(Alignment.CENTRE);
        titleStyle.setVerticalAlignment(jxl.format.VerticalAlignment.getAlignment(1));
        titleStyle.setBorder(Border.NONE, BorderLineStyle.NONE, Colour.BLACK);

        WritableCellFormat colmStyle = new WritableCellFormat(new WritableFont(WritableFont.createFont("微软雅黑"), 10, WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE, Colour.BLACK));
//        title.setBackground(Colour.VERY_LIGHT_YELLOW);
        colmStyle.setAlignment(Alignment.CENTRE);
        colmStyle.setVerticalAlignment(jxl.format.VerticalAlignment.getAlignment(1));
        colmStyle.setBorder(Border.NONE, BorderLineStyle.NONE, Colour.BLACK);

        for(int i = 0;i < titles.size();i++){
            //x,y,第一行的列名
            label=new Label(i,0,titles.get(i),titleStyle);
            //7：添加单元格
            sheet.addCell(label);
        }

        for(int i = 1;i <= resultList.size();i++){
            BusinessStatisticsVo businessStatistics = (BusinessStatisticsVo) resultList.get(i - 1);
            label=new Label(0,i, String.valueOf(i),colmStyle);
            sheet.addCell(label);
            label=new Label(1,i, businessStatistics.getWyName(), colmStyle);
            sheet.addCell(label);
            String curDate = "";
            if (null != businessStatistics.getAccess_date()) {
                curDate = sdf.format(businessStatistics.getAccess_date());
            }
            label=new Label(2,i, curDate, colmStyle);
            sheet.addCell(label);
            label=new Label(3,i, String.valueOf(businessStatistics.getAccess_count()), colmStyle);
            sheet.addCell(label);

        }
        ExcelUtil.setColumnAutoSize(sheet,10);

        workbook.write();
        workbook.close();
    }


    public void exportExcelByBusinessStatistics(String fileName, String sheetName, List resultList) throws Exception {
        WritableWorkbook workbook = jxl.Workbook.createWorkbook(new File(fileName));
        WritableSheet sheet = workbook.createSheet(sheetName, 0);
        WritableFont wf = new WritableFont(WritableFont.TIMES,18,WritableFont.BOLD,true);
        WritableCellFormat wcf = new WritableCellFormat(wf);
        wcf.setBorder(jxl.format.Border.ALL,jxl.format.BorderLineStyle.THIN);
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd") ;
        //设置title
        List<String> titles = new ArrayList();
        titles.add("序号");
        titles.add("物业名称");
        titles.add("商户名称");
        titles.add("商户编号");
        titles.add("日期");
        titles.add("访问人数");


        Label label=null;

        WritableCellFormat titleStyle = new WritableCellFormat(new WritableFont(WritableFont.createFont("微软雅黑"), 11, WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE, Colour.BLACK));
        titleStyle.setBackground(Colour.SEA_GREEN);
        titleStyle.setAlignment(Alignment.CENTRE);
        titleStyle.setVerticalAlignment(jxl.format.VerticalAlignment.getAlignment(1));
        titleStyle.setBorder(Border.NONE, BorderLineStyle.NONE, Colour.BLACK);

        WritableCellFormat colmStyle = new WritableCellFormat(new WritableFont(WritableFont.createFont("微软雅黑"), 10, WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE, Colour.BLACK));
//        title.setBackground(Colour.VERY_LIGHT_YELLOW);
        colmStyle.setAlignment(Alignment.CENTRE);
        colmStyle.setVerticalAlignment(jxl.format.VerticalAlignment.getAlignment(1));
        colmStyle.setBorder(Border.NONE, BorderLineStyle.NONE, Colour.BLACK);

        for(int i = 0;i < titles.size();i++){
            //x,y,第一行的列名
            label=new Label(i,0,titles.get(i),titleStyle);
            //7：添加单元格
            sheet.addCell(label);
        }

        for(int i = 1;i <= resultList.size();i++){
            BusinessStatisticsVo businessStatistics = (BusinessStatisticsVo) resultList.get(i - 1);
            label=new Label(0,i, String.valueOf(i),colmStyle);
            sheet.addCell(label);
            label=new Label(1,i, businessStatistics.getWyName(), colmStyle);
            sheet.addCell(label);
            label=new Label(2,i, businessStatistics.getBusiness_name(), colmStyle);
            sheet.addCell(label);
            label=new Label(3,i, String.valueOf(businessStatistics.getBusiness_bh()),colmStyle);
            sheet.addCell(label);
            String curDate = "";
            if (null != businessStatistics.getAccess_date()) {
                curDate = sdf.format(businessStatistics.getAccess_date());
            }
            label=new Label(4,i, curDate, colmStyle);
            sheet.addCell(label);
            label=new Label(5,i, String.valueOf(businessStatistics.getAccess_count()), colmStyle);
            sheet.addCell(label);

        }
        ExcelUtil.setColumnAutoSize(sheet,10);

        workbook.write();
        workbook.close();
    }


    public IPage<BusinessStatisticsVo> getWyStatisticsDetail(String wyId, String queryDate, Integer page, Integer limit) throws ParseException {
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        Date date = sdf.parse(queryDate);
        String dateStr = sdf.format(date);
        Page p = new Page(page, limit);
        IPage<BusinessStatisticsVo> iPage = businessStatisticsDao.getWyStatisticsDetail(p, wyId, dateStr);
        return iPage;
    }

}
